At 12:37 AM +0200 6/4/07, PFC wrote:
>>Yeah, it is awful ;^) However the existing system is equally awful
>>because there is no way to enter NULL!
>
>Consider this form :
>
>First name : Edgar
>Middle name : J.
>Last name : Hoover
>
>Now, if someone has no middle name, like "John Smith", should we use
>NULL or "" for the middle name ?
>NULL usually means "unknown" or "not applicable", so I believe we
>have to use the empty string here. It makes sense to be able to
>concatenate the three parts of the name, without having to put
>COALESCE() everywhere.
>
>Now consider this form :
>
>City :
>State :
>Country :
>
>If the user doesn't live in the US, "State" makes no sense, so it
>should be NULL, not the empty string. There is no unnamed state.
>Also, if the user does not enter his city name, this does not mean
>he lives in a city whose name is "". So NULL should be used, too.
>
>It is very context-dependent.
My take on the NULL philosophy is that NULL should indicate that no
data has been entered. If the data for the record is not applicable,
then it should have a zero length string, indicating that the field
has been considered by the user, and that a blank value is
appropriate. A NULL field on an entered record should indicate an
error condition, rather than that the field is not appropriate to the
context.
Thus, NULL fields on a completed record would mean either that they
were never presented to the user (thus, did not appear in the UI), or
an error condition.
The advantages to this is that, if enforced, a count of the non-null
records will show those operated on by a user, vs. those untouched by
a user.
-Owen